namespace TSL.DAL
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using TSL.Model;

    public class Banks
    {
        public void Accept(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Bank_Accept");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, id);
            db.ExecuteNonQuery(dbCommand);
        }

        public void AddBanks(TSL.Model.Banks model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Banks_ADD");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "bank", DbType.AnsiString, model.bank);
            db.AddInParameter(dbCommand, "BankNumber", DbType.AnsiString, model.BankNumber);
            db.AddInParameter(dbCommand, "BankNumbers", DbType.AnsiString, model.BankNumbers);
            db.AddInParameter(dbCommand, "BankURL", DbType.AnsiString, model.BankURL);
            db.AddInParameter(dbCommand, "BankImage", DbType.AnsiString, model.BankImage);
            db.AddInParameter(dbCommand, "memo", DbType.AnsiString, model.memo);
            db.AddInParameter(dbCommand, "ShowImage", DbType.AnsiString, model.ShowImage);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Delete(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Banks_Delete");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            db.ExecuteNonQuery(dbCommand);
        }

        public void DeleteBanks(string ID)
        {
            List<string> strList = new List<string>();
            strList.Add("delete from Banks where ID = '" + ID + "'");
            DbHelperSQL.ExecuteSqlTran(strList);
        }

        public bool Exists(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Banks");
            strSql.Append(" where BankNumber=@name ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@name", SqlDbType.VarChar, 50) };
            parameters[0].Value = name;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public bool Exists(string ssc_id, string lottery_num)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Banks");
            strSql.Append(" where lottery_num=@lottery_num and ssc_id=@ssc_id");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ssc_id", SqlDbType.VarChar, 50), new SqlParameter("@lottery_num", SqlDbType.VarChar, 50) };
            parameters[0].Value = ssc_id;
            parameters[1].Value = lottery_num;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public DataSet GetBankModels()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_GetBanks");
            return db.ExecuteDataSet(dbCommand);
        }

        public DataSet GetBuyHis(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 5 * from v_Banks");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public List<TSL.Model.Banks> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Banks ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            List<TSL.Model.Banks> list = new List<TSL.Model.Banks>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public DataSet GetListFromView(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Banks ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public TSL.Model.Banks GetModel(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Banks_GetModelByID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            TSL.Model.Banks model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Banks GetModel(string name, string password)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Banks_GetModel");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, name);
            db.AddInParameter(dbCommand, "password", DbType.AnsiString, password);
            TSL.Model.Banks model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Banks GetModels(string bank)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_GetBanks");
            db.AddInParameter(dbCommand, "bank", DbType.AnsiString, bank);
            TSL.Model.Banks model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Banks ReaderBind(IDataReader dataReader)
        {
            TSL.Model.Banks model = new TSL.Model.Banks();
            model.id = dataReader["id"].ToString();
            model.username = dataReader["username"].ToString();
            model.bank = dataReader["bank"].ToString();
            model.BankNumbers = dataReader["BankNumbers"].ToString();
            model.BankNumber = dataReader["BankNumber"].ToString();
            model.BankURL = dataReader["BankURL"].ToString();
            model.BankImage = dataReader["BankImage"].ToString();
            model.state = dataReader["state"].ToString();
            model.memo = dataReader["memo"].ToString();
            model.ShowImage = dataReader["ShowImage"].ToString();
            return model;
        }

        public void Update(TSL.Model.Banks model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Banks_Update");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "bank", DbType.AnsiString, model.bank);
            db.AddInParameter(dbCommand, "BankNumber", DbType.AnsiString, model.BankNumber);
            db.AddInParameter(dbCommand, "BankNumbers", DbType.AnsiString, model.BankNumbers);
            db.AddInParameter(dbCommand, "BankURL", DbType.AnsiString, model.BankURL);
            db.AddInParameter(dbCommand, "BankImage", DbType.AnsiString, model.BankImage);
            db.AddInParameter(dbCommand, "memo", DbType.AnsiString, model.memo);
            db.AddInParameter(dbCommand, "ShowImage", DbType.AnsiString, model.ShowImage);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}

